<?php
/**
 * Created by PhpStorm.
 * User: Administrator
 * Date: 2018/8/15 0015
 * Time: 11:44
 */

namespace app\components;

use \Exception;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\IOFactory;

/**
 * Class Excel
 * 对PHPExcel 库的封装
 *
 * @private Spreadsheet $excel_obj       读取excel文件 或 生成excel文件 时创建的PHPExcel对象
 * @private string    $excel_file_path 读取excel文件 或 生成excel文件 的路径
 * @private array     $title           读取excel文件 或 生成excel文件 的标题行（第一行） 格式为 [ 'id' , 'name' , 'title' ]
 * @private array     $data            读取excel文件 或 生成excel文件 的数据 格式为 [ [1,2,3] , [2,3,4] , [3,4,5] ]
 * @private array     $indexData       读取excel文件 或 生成excel文件 的带有单元格名称索引的数据，格式为 [ 'A1'=>'1' , 'A2'=>'2' ,'B1'=>'1' ,'B2'=>'2' ]
 *
 * demo:
 *  try{
 *       $title = ['id','name','title'];
 *       $data = [
 *           [1,'text','text'],
 *           [2,'text','text'],
 *       ];
 *
 *       $excel = new Excel();
 *
 *       //数据导出excel：
 *       $excel->export($data,$title)->save('test.xlsx');
 *       // or
 *       $excel->setTitle($title)->setData($data)->export()->save('test.xlsx');
 *       //todo with test.xlsx;
 *
 *       //excel导入
 *       $data = $excel->import('test.xlsx',true)->getData();
 *       //todo with $data
 *
 *  }catch (Exception $e){
 *  }
 */
class Excel
{
    private $excel_obj;

    private $excel_file_path;

    private $title = [];

    private $data = [];

    private $indexData = [];

    /**
     * Excel constructor.
     * @param string $excel_file_path
     * @throws Exception
     */
    public function __construct($excel_file_path = '')
    {
        if (!$excel_file_path) {
            $this->excel_obj = new Spreadsheet();
        } else {
            $this->excel_file_path = $excel_file_path;
            $this->excel_obj = IOFactory::load($this->excel_file_path);
        }
    }

    public function getExcelObj()
    {
        return $this->excel_obj;
    }

    /**
     * @return array
     */
    public function getTitle()
    {
        return $this->title;
    }

    /**
     * @param array $title
     * @return Excel
     */
    public function setTitle($title)
    {
        $this->title = $title;
        return $this;
    }

    /**
     * @return array
     */
    public function getData()
    {
        return $this->data;
    }

    /**
     * @param array $data
     * @return Excel
     */
    public function setData($data)
    {
        $this->data = $data;
        return $this;
    }

    /**
     * @return string
     */
    public function getExcelFilePath()
    {
        return $this->excel_file_path;
    }

    /**
     * @param string $excel_file_path
     * @return Excel
     */
    public function setExcelFilePath($excel_file_path)
    {
        $this->excel_file_path = $excel_file_path;
        return $this;
    }

    /**
     * @return array
     */
    public function getIndexData()
    {
        return $this->indexData;
    }

    /**
     * 设置sheet的title
     * @param $name
     * @throws Exception
     */
    public function setSheetTitle($name)
    {
        if (!$this->excel_obj || !($this->excel_obj instanceof Spreadsheet)) {
            throw new Exception('excel对象错误');
        }
        $this->excel_obj->getActiveSheet()->setTitle($name);
    }

    /**
     * 切换活动sheet页面
     * @param $sheetIndex
     * @throws Exception
     */
    public function checkSheet($sheetIndex)
    {
        if (!$this->excel_obj || !($this->excel_obj instanceof Spreadsheet)) {
            throw new Exception('excel对象错误');
        }
        $this->excel_obj->setActiveSheetIndex($sheetIndex);
    }

    /**
     * 获取sheet页面对象
     * @param $sheetIndex
     * @return Worksheet
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     */
    public function getSheetObj($sheetIndex)
    {
        if (!$this->excel_obj || !($this->excel_obj instanceof Spreadsheet)) {
            throw new Exception('excel对象错误');
        }
        return $this->excel_obj->getSheet($sheetIndex);
    }

    private function convertUTF8($str)
    {
        if (!$str) return $str;
        $encode = mb_detect_encoding($str, ['ASCII', 'GB2312', 'GBK', 'UTF-8']);
        return $encode == 'UTF-8' ? $str : mb_convert_encoding($str, 'utf-8', $encode);
    }

    /**
     * 根据列数，计算列名
     * @param $columnIndex
     * @return string
     * @throws Exception
     */
    private function getColumnName($columnIndex): string
    {
        if ($columnIndex > 26) {
            $column_1 = intval(floor($columnIndex / 26));
            $column_2 = $columnIndex % 26;
            if ($column_2 == 0) {
                $column_1--;
                $column_2 = 26;
            }
            return $this->getColumnName($column_1) . $this->getColumnName($column_2);
        } else {
            return chr(64 + $columnIndex);
        }
    }

    /**
     * 导出excel
     * title可以为空,当title为空时，则按照data内数据进行导出。
     *
     * 需要注意的是，当title不为空时，title与data的格式问题
     * 支持未指定键名，仅靠数组内数据顺序的非严格匹配。例如
     *      $title = ['id','name','title'];
     *      $data = [
     *                  [1,'text','text'],
     *                  [2,'text','text'],
     *              ];
     * 同时也支持指定键名，靠键名进行匹配。此时data内数据的顺序可以打乱，键名正确即可。例如
     *      $title = ['id'=>'ID','name'=>'姓名','title'=>'title'];
     *      $data = [
     *                  ['id'=>1,'name'=>'text','title'=>'text'],
     *                  ['title'=>'text','name'=>'text','id'=>2]
     *              ];
     *
     * ！！！！ 但要注意，两种方式只能取其一，无法交叉使用。 ！！！！
     * 例如
     *      $title = ['id','name','title']; $data = ['id'=>1,'name'=>'text','title'=>'text']; 则会出现没有数据的情况
     * 同样 如果
     *      $title = ['id'=>'ID','name'=>'姓名','title'=>'title']; $data = [1,'text','text']; 也会出现没有数据的情况
     *
     * @param array $data 生成excel文件的数据
     * @param array $title 生成excel文件的标题行（第一行）
     * @return Excel
     * @throws Exception
     */
    public function export($data, $title = []): Excel
    {
        $this->title = empty($title) ? $this->title : $title;
        $this->data = empty($data) ? $this->data : $data;
        if (empty($this->data) && empty($this->indexData)) {
            throw new Exception('数据不合法');
        }
        $activeSheet = $this->excel_obj->getActiveSheet();
        if (empty($this->indexData)) {   //优先解析indexData，若没有值，则根据data解析
            $rowIndex = 1;
            $columnIndex = 1;
            $key_index = [];
            if (!empty($title)) {
                //若title不为空，则遍历title，将title写入第一行，同时建立key索引
                foreach ($title as $key => $value) {
                    $key_index[] = $key;
                    $columnName = $this->getColumnName($columnIndex);
                    $activeSheet->setCellValue($columnName . $rowIndex, $value);
                    $columnIndex++;
                }
                $rowIndex++;
            }
            foreach ($this->data as $row) {
                $columnIndex = 1;
                if (empty($key_index)) {
                    //key索引为空，则直接遍历row，根据row中数据的位置填充数据
                    foreach ($row as $value) {
                        $columnName = $this->getColumnName($columnIndex);
                        $activeSheet->setCellValue($columnName . $rowIndex, $value);
                        $columnIndex++;
                    }
                } else {
                    //key索引不为空，根据key索引查找row中的数据
                    foreach ($key_index as $key) {
                        $columnName = $this->getColumnName($columnIndex);
                        $activeSheet->setCellValue($columnName . $rowIndex, isset($row[$key]) ? $row[$key] : '');
                        $columnIndex++;
                    }
                }
                $rowIndex++;
            }
        } else {
            foreach ($this->indexData as $cellName => $value) {
                $activeSheet->setCellValue($cellName, $this->convertUTF8($value));
            }
        }
        return $this;
    }

    /**
     * 导入excel
     * @param string $excel_file_path 导入excel的文件路径
     * @param bool $title 表格是否有title行，默认false，则第一行直接作为数据
     * @param int $sheetIndex excel的sheet页索引，默认是第一个sheet页
     * @param \Closure $callback
     * @return Excel
     * @throws Exception
     */
    public function import($excel_file_path = '', $title = false, $sheetIndex = 0, \Closure $callback = null)
    {
        $this->excel_file_path = $excel_file_path ? $excel_file_path : $this->excel_file_path;
        if (!$this->excel_file_path) {
            throw new Exception('文件地址不能为空');
        }
        $this->excel_obj = IOFactory::load($this->excel_file_path);

        $sheet = $this->getSheetObj($sheetIndex);

        foreach ($sheet->getRowIterator() as $rowIndex => $row) {
            $cellIterator = $row->getCellIterator();
            $cellIterator->setIterateOnlyExistingCells(false);
            $rowData = [];
            foreach ($cellIterator as $columnIndex => $cell) {  //遍历列
                if (!is_null($cell)) {  //如果列不给空就得到它的坐标和计算的值
                    $value = $cell->getValue();
                    $value = $callback != null && $callback instanceof \Closure ? $callback($value, $rowIndex, $columnIndex) : $value;
                    $rowData[] = $value;
                    $this->indexData[$columnIndex . $rowIndex] = $value;
                }
            }
            $rowIndex == 1 && $title === true ? $this->title[] = $rowData : $this->data[] = $rowData;
        }
        return $this;
    }

    /**
     * 保存excel文件
     * @param string $path
     * @throws Exception
     */
    public function save($path = '')
    {

        $this->excel_file_path = $path ? $path : $this->excel_file_path;

        if (!$this->excel_obj || !($this->excel_obj instanceof Spreadsheet)) {
            throw new Exception('excel对象错误');
        }
        if (!$this->excel_file_path) {
            throw new Exception('文件地址不能为空');
        }

        $objWriter = new Xlsx($this->excel_obj);
        $objWriter->save($this->excel_file_path);
    }
//
//    public function output($file_name)
//    {
//        ob_end_clean();
//        ob_start();
//
//        header('Content-Type : application/vnd.ms-excel');
//        header('Content-Disposition:attachment;filename="' . $file_name . '.xlsx"');
//        $objWriter = IOFactory::createWriter($this->excel_obj, 'Xlsx');
//        $objWriter->save('php://output');
//    }

}